package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import myBeans.MenuInfo;
import myBeans.UserInfo;

public class UserRepository {
	private Connection conn;

	public UserRepository() throws NamingException, SQLException {
		InitialContext context = new InitialContext();
		DataSource ds = (DataSource) context
				.lookup("java:comp/env/jdbc/sharewithme");

		this.conn = ds.getConnection();
	}

	public boolean IsExistsUser(String UserID) {
		try {
			Statement stmt = conn.createStatement();

			String sql = "select UserID from TBUser Where UserID = '" + UserID
					+ "'";
			ResultSet rs = stmt.executeQuery(sql);

			if (rs.next()) {
				// ����� row �ʴ���� user ���
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return true;

	}

	public UserInfo GetUserInfo(String UserId) {
		try {
			Statement stmt = conn.createStatement();
			String sql = "Select UserID, Password, Name, DisplayName, MobileNo,UserRole";
			sql += " FROM tbuser";
			sql += " Where UserID='" + UserId + "'";

			ResultSet rs = stmt.executeQuery(sql);

			if (rs.next()) { // if user and password is valid.
				UserInfo user = new UserInfo();

				user.setUserId(rs.getString("UserID"));
				user.setPassword(rs.getString("Password"));
				user.setUserName(rs.getString("Name"));
				user.setDisplayName(rs.getString("DisplayName"));
				user.setMobileNo(rs.getString("MobileNo"));
				user.setRole(rs.getString("UserRole"));

				List<MenuInfo> menus = new MenuRepository(conn)
						.GetMenuByUser(UserId);
				user.setMenus(menus);

				return user;
			} else { // 0 row = user and password is not valid
				return null;
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		return null;
	}

	public boolean CreateMember(UserInfo User) {
		try {
			Statement stmt = conn.createStatement();

			if (IsExistsUser(User.getUserId())) {
				return false;
			}

			//String password = new RandomPassword(5).nextString();

			String sql = "INSERT INTO tbuser (UserID,Password,Name, "
					+ "DisplayName, MobileNo,UserRole)VALUES( " + "'"
					+ User.getUserId() + "','" + User.getPassword() + "','"
					+ User.getUserName() + "','" + User.getDisplayName()
					+ "','" + User.getMobileNo() + "','" + User.getRole()
					+ "')";

			String sql2 = "INSERT INTO tbroleuser (UserID, RoleID) VALUES ('"
					+ User.getUserId() + "', '"+User.getRole()+"')";

			if ((stmt.executeUpdate(sql) > 0) && (stmt.executeUpdate(sql2) > 0)) {
				// send email
				ShareWithMeMail mail = new ShareWithMeMail();
				mail.SendMail();

				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public String getUserIDFromDisplayName(String DisplayName) {
		try {
			String sql = "select userid from sharewithme.tbuser where displayname = '"
					+ DisplayName+"'";

			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				return rs.getString("userid");
			}
		} catch (SQLException e) {
			System.out
					.println("Exception CommentRespository.getUserIDFromDisplayName : "
							+ e.getMessage());
			e.printStackTrace();
		}
		return "";

	}
}
